<?php
/**
 * @package scottlib.core
 * @author Aaron Collegeman aaroncollegeman.com
 * @version 1.0.0-BETA
 * 
 * Copyright (C)2009 Collegeman.net, LLC.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along
 * with this program; if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. 
 */

///////////////////////////////////////////////////////////////////////////////
// This file provides wrappers for native connection drivers.
///////////////////////////////////////////////////////////////////////////////

/**
 * Standard connection interface.
 */
abstract class slConn {
	
	protected $conn;
	
	protected $host;
	
	protected $user;
	
	protected $pass;
	
	protected $db;
	
	protected $level = 0;
	
	protected static $instance;
	
	/**
	 * @param $args
	 * @return Resource, a connection to the database
	 */
	abstract function connect();
	
	abstract function disconnect();
	
	/**
	 * @param $args
	 * @return slResultSet, a result set implementation
	 */
	abstract function query($args);
	
	/**
	 * @param $args
	 * @return Integer, number of rows affected
	 */
	abstract function execute($args);
	
	/**
	 * @param $args
	 * @return mixed, The value of the first column of the first record returned by the query
	 */
	abstract function val($args);
	
	/**
	 * Begin a transaction
	 */
	abstract function begin();
	
	/**
	 * Rollback a transaction
	 */
	abstract function rollback();
	
	/**
	 * Commit a transaction
	 */
	abstract function commit();
	
	/**
	 * Tell the underlying resource to select a specific database
	 * @param $name The name of the database to select
	 */
	abstract function database($name);
	
	abstract function quoteField($field);
		
	abstract function escapeValue($value);
	
	abstract function quoteValue($value);
	
	abstract function quoteNumeric($value);
	
	/**
	 * This method provides escaping for arrays. Note that it supports only one dimensional
	 * arrays: Any value of the array that is itself an array or an object will be serialized 
	 * with the standard PHP serialize() function.
	 * @param Array $arr An array whose values should be escaped for insertion into a query
	 * @return A list, suitable for insertion into an ANSI SQL query
	 * @see serialize()
	 */
	final function arrayToList($arr) {
		$values = array();
		foreach ($arr as $k => $v) {
			if (is_object($v) || is_array($v))
				$values[] = $this->quoteValue($this->escapeValue(serialize($v)));
			else if (is_string($v))
				$values[] = $this->quoteValue($this->escapeValue($v));
			else
				$values[] = $v;
		}
		return "(".join(",", $values).")";
	}
	
	/**
	 * @return slConn, The last slConn instance created by the owning Thread.
	 */
	static function instance() {
		if (!self::$instance)
			throw new Exception("No instance has yet been created.");
			
		return self::$instance;
	}
	
	///////////////////////////////////////////////////////////////////////////
	// Setters
	///////////////////////////////////////////////////////////////////////////
	
	final function setHost($host) {
		$this->host = $host;
	}
	
	final function setUser($user) {
		$this->user = $user;
	}
	
	final function setPass($pass) {
		$this->pass = $pass;
	}
	
	final function setDb($db) {
		$this->db = $db;
	}
	
}

/**
 * MySql connection wrapper.
 */
final class slMySqlConn extends slConn {
	
	private $client_flags;
	
	private $new_link = true;
	
	function __construct($args = null) {
		$args = is_array($args) ? $args : func_get_args();
		$this->setHost(func_parse_args($args, "host", 0, false));
		$this->setUser(func_parse_args($args, "user", 1, false));
		$this->setPass(func_parse_args($args, "pass", 2, false));
		$this->setDb(func_parse_args($args, "db", 3, false));
	}
	
	function connect() {
		ob_start();
		
		$this->conn = mysql_connect($this->host, $this->user, $this->pass, $this->new_link, $this->client_flags);
		
		$error = ob_get_clean();
		
		if ($this->conn === false)
			throw new Exception("Failed to connect to server, $this->user:".preg_replace('/./', '*', $this->pass)."@$this->host. ".trim($error));
		
		if (!empty($this->db))
			$this->database($this->db);
			
		slConn::$instance = $this;
	}
	
	function disconnect() {
		mysql_close($this->conn);	
	}
	
	function query($sql) {
		if (($resource = @mysql_query("$sql", $this->conn)) === false)
			throw new Exception("Failed to execute query: ".mysql_error($this->conn));
		return new slMySqlResultSet($resource);
	}
	
	function execute($sql) {
		if (!@mysql_query("$sql", $this->conn))
			throw new Exception("Failed to execute query: ".mysql_error($this->conn));
		return mysql_affected_rows($this->conn);
	}
	
	function val($sql) {
		return $this->query($sql)->val();
	}
	
	function begin() {
		if ($this->level === 0) {
			$this->execute('SET AUTOCOMMIT=0');
			$this->execute('BEGIN');	
		}
		$this->level++;
	}
	
	function commit() {
		$this->level--;
		if ($this->level === 0) {
			$this->execute('COMMIT');
			$this->execute('SET AUTOCOMMIT=1');
		}
	}
	
	function rollback() {
		if ($this->level > 0) {
			$this->level = 0;
			$this->execute('ROLLBACK');
			$this->execute('SET AUTOCOMMIT=1');
		}
	}
	
	function database($name) {
		if (!@mysql_select_db($name, $this->conn))
			throw new Exception("Failed to connect to database $name: ".mysql_error($this->conn));	
		return $this;
	}
	
	function quoteField($field) {
		return "`$field`";
	}
	
	function quoteValue($value) {
		return "'$value'";
	}
	
	function escapeValue($value) {
		return mysql_escape_string($value);
	}
	
	function quoteNumeric($value) {
		return $value;
	}
	
}

/**
 * Processes various forms of parameterized SQL statements, replacing a variety of placeholders
 * (some generic and some data-type specific, or on a named basis) with the values specified
 * by $args.
 *
 * In addition to value parameters, this class also supports table and column name insertion, 
 * as well as pass-through parameters, which are inserted with neither escaping nor delimiting. 
 *
 * When the namespace is available, the function prepare() can be used to instantiate this class.
 */
final class slPreparedStatement {
	
	private $sql;
	
	private $conn;
	
	function __toString() {
		return $this->sql;
	}
	
	function getSql() {
		return $this->sql;
	}
	
	/**
	 * This method provides escaping for arrays. Note that it supports only one dimensional
	 * arrays.  Any value of the array that is itself an array or an object will be
	 * serialized with the standard PHP serialize() function.
	 * @param Array $arr An array whose values should be escaped for insertion into a MySQL query
	 * @return A new array, fully escaped.
	 * @see mysql_esacpe_string()
	 * @see serialize()
	 */
	private static function escapeArray($arr, slConn $conn) {
		$values = array();
		foreach ($arr as $k => $v) {
			if (is_numeric($v))
				$values[] = $v;
			else if (is_object($v) || is_array($v))
				$values[] = $conn->quoteValue($conn->escapeValue(serialize($v)));
			else
				$values[] = $conn->quoteValue($conn->escapeValue($v));
		}
		return join(",", $values);
	}

	/**
	 * @param slConn A database connection, for escaping
	 * @param String $sql A parameterized SQL statement
 	 * @param ... mixed $args Values to be inserted into $sql
 	 * @throws Exception When the number of placeholders in $sql is not equal to the number of $args
	 * @throws Exception When a named placeholder does not have a corresponding value in $args
	 */
	function __construct(slConn $conn, $sql, $args = null) {
		
		$this->conn = $conn;
		
		// anything to parse?
		if (count($args)) {
			
			///////////////////////////////////////////////////////////////////
			// Named parameter mode
			///////////////////////////////////////////////////////////////////
			
			if (!array_all_keys_numeric($args)) { 
				
				// if there are parameters in the sql query, then replace them
				if (preg_match_all('/(\!)?([\:\#])([\w\_]+)\b/i', $sql, $matches, PREG_OFFSET_CAPTURE)) {
					list($fulltext, $optionals, $types, $placeholders) = $matches;  
					
					for($i=count($placeholders)-1; $i>=0; $i--) {
						list($type) = $types[$i];
						$optional = $optionals[$i];
						list ($placeholder, $offset) = $placeholders[$i];	
						
						// standard named parameter:
						if ($type == ':') {
							
							// optional named parameter: use null in place of missing parameter values
							if (!isset($args[$placeholder])) {
								if ($optional)
									$sql = strpunch($sql, 'null', $offset-2, strlen($placeholder)+2);
								else
									throw new Exception("Parameter value not provided for placeholder [$placeholder].");
							}
							
							// required parameters:
							else {
								$value = $args[$placeholder];
								if (is_numeric($value))
									$sql = strpunch($sql, $value, $offset-1, strlen($placeholder)+1);
								else {
									$sql = strpunch($sql, $this->conn->quoteValue($this->conn->escapeValue($value)), $offset-1, strlen($placeholder)+1);
								}
							}
						}
						
						else if ($type == '#') {
							if (!isset($args[$placeholder]))
								throw new Exception("Parameter value not provided for placeholder [$placeholder].");
							
							$value = $args[$placeholder];
							$sql = strpunch($sql, $this->conn->quoteField($value), $offset-1, strlen($placeholder)+1);
						}
					}
				}
			}
			
			///////////////////////////////////////////////////////////////////
			// Anonymous parameter mode
			///////////////////////////////////////////////////////////////////
			
			else {
				
				// if there are parameters in the sql query, then punch them
				if (preg_match_all('/([\?\#]|%[dcsapnb])/', $sql, $matches, PREG_OFFSET_CAPTURE)) { 
					
					$placeholders = $matches[0];
					if (($npl = count($placeholders)) != ($npa = count($args)))
						throw new Exception("Number of parameters ($npa) does not equal number of placeholders ($npl)");
					
					for($i=count($placeholders)-1; $i>=0; $i--) {
						
						list($placeholder, $offset) = $placeholders[$i];
						$value = $args[$i];
	
						// value is null
						if ($value === null)
							$sql = strpunch($sql, 'null', $offset, strlen($placeholder));
						
						// expecting boolean value:
						else if ($placeholder == '%b') {
							if (!is_bool($value) && !is_bool( ($value ? true : false) ))
								throw new Exception("Expecting boolean for parameter $i, found: ".$value."; $sql");
							$sql = strpunch($sql, ($value ? 1 : 0), $offset, 2);	
						}
							
						// expecting array parameter value:
						else if ($placeholder == '%a') {
							if (!is_array($value))
								throw new Exception("Expecting array for parameter $i, found: ".$value."; $sql");
							$sql = strpunch($sql, self::escapeArray($value, $this->conn), $offset, 2);
						}
						
						// expecting numeric value:
						else if ($placeholder == '%d' || $placeholder == '%n') {
							if (!is_numeric($value))
								throw new Exception("Expecting numeric for parameter $i, found: ".$value."; $sql");	
							$sql = strpunch($sql, $value, $offset, 2);
						}
						
						// expecting date value:
						else if ($placeholder == '%c') {
							// timestamp?
							if (is_numeric($value)) {
								if (($date = date('Y/m/d H:i:s', $value)) === false)
									throw new Exception("Failed to create date from timestamp: ".$value."; $sql");
							}
							
							// strtotime parsable value?
							else { 
								if (($timestamp = strtotime($value)) === false)
									throw new Exception("Failed to parse date from: ".$value."; $sql");
								$date = date('Y/m/d H:i:s', $timestamp);
							}
							
							$sql = strpunch($sql, $this->conn->quoteValue($date), $offset, 2);
						}
						
						// expecting string value:
						else if ($placeholder == '%s') {
							if (!is_string($value) && !is_numeric($value))
								throw new Exception("Expecting string for parameter $i, found: ".$value."; $sql");
							$escaped = $this->conn->quoteValue($this->conn->escapeValue($value));
							$sql = strpunch($sql, $escaped, $offset, 2); 	
						}
						
						// punch-through and numeric parameter values:
						else if ($placeholder == '%p') {
							$sql = strpunch($sql, $value, $offset, 2);	
						}
						
						// no escaping: 
						else if ($placeholder == '#') { 
							$sql = strpunch($sql, $this->conn->quoteField($value), $offset);
						}
						
						// numeric parameter value:
						else if (is_numeric($value)) {
							$sql = strpunch($sql, $this->conn->quoteNumeric($value), $offset);	
						}
						
						// escaping:
						else { 
							$escaped = $this->conn->quoteValue($this->conn->escapeValue($value));
							$sql = strpunch($sql, $escaped, $offset);
						}
					}
				}
			}
		}
		
		$this->sql = $sql;
	}
}


///////////////////////////////////////////////////////////////////////////////
// Function definitions
///////////////////////////////////////////////////////////////////////////////

function sl_conn_and_sql($func_args) {
	// first function argument will be a sql statement
	$sql = array_shift($func_args);
	
	// first arg might be a connection
	$conn = null;
	if (count($func_args) >= 1) {
		if ($func_args[0] instanceof slConn)
			$conn = array_shift($func_args);
	}
	
	if (!$conn)
		$conn = slConn::instance();

	// whatever is left might be a hash, or it might be a series of parameter values
	if (count($func_args) > 0) {
		
		if (count($func_args) == 1 && is_array($func_args[0]) && !array_all_keys_numeric($func_args[0])) { // it's a hash!
			$func_args = $func_args[0];
		}

		$stmt = new slPreparedStatement($conn, $sql, $func_args);
		$sql = $stmt->getSql();
	}	
	
	return array($sql, $conn);
}

if (!function_exists('connect')) {
	function connect($args) {
		$args = is_array($args) ? $args : func_get_args();
		$type = func_parse_args($args, 'type', 5, false, 'slMySqlConn');
		$conn = new $type($args);
		$conn->connect();
		return $conn;
	}
}

if (!function_exists('db')) {
	function db() {
		return slConn::instance();
	}
}

if (!function_exists('disconnect')) {
	
	function disconnect() {
		slConn::instance()->disconnect();
	}
}

if (!function_exists('prepare')) {
	function prepare($conn = null, $sql = null, $args = null) {
		list($sql, $conn) = sl_conn_and_sql(func_get_args());
		return $sql;
	}
}

if (!function_exists('query')) {
	function query($conn = null, $args = null) {
		list($sql, $conn) = sl_conn_and_sql(func_get_args());
		return $conn->query($sql);
	}
}

if (!function_exists('val')) {
	function val($conn = null, $args = null) {
		list($sql, $conn) = sl_conn_and_sql(func_get_args());
		return $conn->val($sql);
	}
}

if (!function_exists('execute')) {
	function execute($conn = null, $args = null) {
		list($sql, $conn) = sl_conn_and_sql(func_get_args());
		return $conn->execute($sql);
	}
}

if (!function_exists('begin')) {
	function begin(slConn $conn = null) {
		if (!$conn)
			$conn = slConn::instance();
		$conn->begin();		
	}
}

if (!function_exists('begin_transaction')) {
	function begin_transaction(slConn $conn = null) {
		if (!$conn)
			$conn = slConn::instance();
		$conn->begin();		
	}
}

if (!function_exists('commit')) {
	function commit(slConn $conn = null) {
		if (!$conn)
			$conn = slConn::instance();
		$conn->commit();		
	}
}

if (!function_exists('rollback')) {
	function rollback(slConn $conn = null) {
		if (!$conn)
			$conn = slConn::instance();
		$conn->rollback();		
	}
}
